package de.velvettear.squirrel.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.UUID;
import java.util.Vector;

import android.os.AsyncTask;
import android.util.Log;
import de.velvettear.squirrel.kernel.Contact;
import de.velvettear.squirrel.kernel.Settings;
import de.velvettear.squirrel.kernel.Statics;

public class MySQLHelper {

	private static Connection connect(String database, String user, String password) {
	
		Connection connection = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			connection = DriverManager.getConnection("jdbc:mysql://" + Statics.MYSQL_HOST + "/" + database + "?user=" + user + "&password=" + password);
		} catch (Exception e) {
			if (e.getMessage() != null) {
				Log.e("connect()", e.getMessage());
			}
		}
		
		return connection;		
	}
	
	public static void executeStatement(String query, String database, String user, String password) {
		final String sqlQuery = query;
		final String databaseName = database;
		final String userName = user;
		final String pw = password;
		
		AsyncTask<Void, Void, Void> updateTask = new AsyncTask<Void, Void, Void>() {

			@Override
			protected Void doInBackground(Void... params) {
				try {
					Connection connection = connect(databaseName, userName, pw);
					Statement statement = connection.createStatement();
											
					statement.execute(sqlQuery);		
					
					statement.close();
					connection.close();	
				} catch (Exception e) {
					if (e.getMessage() != null) {
						Log.e("executeStatement()", e.getMessage());
					}
				}	
				
				return null;
			}
			
		};
		
		updateTask.execute();
	}
		
	public static Vector<Contact> getJabberUsers() {	
		Vector<Contact> contactList = new Vector<Contact>();
		
		String query = "SELECT * " + 
					   "FROM Contacts " + 
					   "WHERE m_sName <> '" + new Settings().getValueByName(Statics.SETTINGS_CHAT_USERNAME) + "' " + 
					   "AND m_bDeleted <> 1 " +
					   "ORDER BY m_sName";
		
		try {
			Connection connection = connect(Statics.MYSQL_JABBER_DATABASE, Statics.MYSQL_JABBER_USER, Statics.MYSQL_JABBER_PASSWORD);
			Statement statement = connection.createStatement();
			
			ResultSet result = statement.executeQuery(query);		
			
			while (result.next()) {
				Contact contact = new Contact(result.getString("m_sName"), result.getString("m_sAlias"));
				contactList.add(contact);
			}
			
			statement.close();
			connection.close();	
		} catch (Exception e) {
			if (e.getMessage() != null) {
				Log.e("executeStatement()", e.getMessage());
			}
		}	
		
		return contactList;
	}
	
	public static void deleteJabberUser(String username) {
		String statement = "UPDATE Contacts " + 
						   "SET m_bDeleted = 1 " + 
						   "WHERE m_sName = '" + username + "'";
		
		executeStatement(statement, Statics.MYSQL_JABBER_DATABASE, Statics.MYSQL_JABBER_USER, Statics.MYSQL_JABBER_PASSWORD);
	}
	
	public static void updateJabberUser(String username, String alias, boolean deleted) {
		String statement = "UPDATE Contacts " +
						   "SET m_sAlias = '" + alias + "' ";
		if (deleted) {
			statement += ", m_bDeleted = 1 ";
		} else {
			statement += ", m_bDeleted = 0 ";
		}
		statement += "WHERE m_sName = '" + username + "'";

		executeStatement(statement, Statics.MYSQL_JABBER_DATABASE, Statics.MYSQL_JABBER_USER, Statics.MYSQL_JABBER_PASSWORD);
	}
	
	public static void createJabberUser(String username, String alias, String password) {
		String statement = "INSERT INTO Contacts " +
						   "(m_sName, m_sAlias, m_sPassword, m_bDeleted) " + 
						   "VALUES " + 
						   "('" + username + "','" + alias + "','" + password +"',0)";
		
		executeStatement(statement, Statics.MYSQL_JABBER_DATABASE, Statics.MYSQL_JABBER_USER, Statics.MYSQL_JABBER_PASSWORD);
	}
	
	public static void createOrUpdateJabberUser(String username, String alias, String password) {
		if (isExistingJabberUser(username)) {
			updateJabberUser(username, alias, false);
		} else {
			createJabberUser(username, alias, password);
		}
	}
	
	public static boolean isExistingJabberUser(String username) {
		String query = "SELECT count(*) " +
					   "FROM Contacts " + 
					   "WHERE m_sName = '" + username + "'";
		
		int count = 0;
		try {
			Connection connection = connect(Statics.MYSQL_JABBER_DATABASE, Statics.MYSQL_JABBER_USER, Statics.MYSQL_JABBER_PASSWORD);
			Statement statement = connection.createStatement();
			
			ResultSet result = statement.executeQuery(query);		
					
			while (result.next()) {
				count = result.getInt(1);
			}
			
			statement.close();
			connection.close();	
			
			
		} catch (Exception e) {
			if (e.getMessage() != null) {
				Log.e("executeStatement()", e.getMessage());
			}
		}	
		
		if (count > 0) {
			return true;
		} else {
			return false;
		}
	}
	
	public static void updateSecurityCamArticle(String title, String date) {
		String refresh = "15000";
		String guid = UUID.randomUUID().toString();
				
		String introtext = 		   "<html>\n" + 
								   "	<head>\n" + 
								   "		<meta http-equiv=\"cache-control\" content=\"max-age=0\" />\n" +
								   "		<meta http-equiv=\"cache-control\" content=\"no-cache\" />\n" +
								   "		<meta http-equiv=\"expires\" content=\"0\" />\n" + 
								   "		<meta http-equiv=\"expires\" content=\"Tue, 01 Jan 1980 1:00:00 GMT\" />\n" +
								   "		<meta http-equiv=\"pragma\" content=\"no-cache\" />\n" + 
								   "		<script>\n" + 
								   "			function auto_refresh() {\n" +
								   "				window.location = \\'http://velvettear.no-ip.org/securitycam\\';\n" +
								   "			}\n" + 
								   "			setTimeout(auto_refresh, " + refresh + ");\n" +
								   "		</script>\n" + 
								   "	</head>\n" + 
								   "	<body>\n" + 
								   "		<h1 style=\"text-align: right;\">SecurityCam</h1>\n" +
								   "		<h3 style=\"text-align: right;\">" + date + "</h3>\n" +
								   "		<p>&nbsp;</p>\n" +
								   "		<p><center><img src=\"ftp://securitycam:securitycam@velvettear.no-ip.org/VelvetTear/SecurityCam/image.jpg?" + guid +"\" alt=\"SecurityCam\"></center></p>\n" +
								   "	</body>\n" +
								   "</html>";
				
		String statement = 		   "UPDATE " + Statics.MYSQL_JOOMLA_PREFIX + "content " +
								   "SET introtext = '" + introtext + "' " +
								   "WHERE title = '" + title + "'";
		executeStatement(statement, Statics.MYSQL_JOOMLA_DATABASE, Statics.MYSQL_JOOMLA_USER, Statics.MYSQL_JOOMLA_PASSWORD);
	}
	
}
